﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace fanli.DAL
{
    /// <summary>
    /// 数据访问类fanli_gift。
    /// </summary>
    public class fanli_gift
    {
        iData.DataAcess worker = new iData.DataAcess();
        public fanli_gift()
        { }

        #region  基本方法（系统生成）

        #region GetMaxId
        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            string sql = "select max(id) from fanli_gift";
            return worker.ExecuteCount(sql);
        }
        /// <summary>
        /// 得到最大ID (如不需要条件，可以设置strWhere为空，paraList为null )
        /// </summary>
        public int GetMaxId(string strWhere, List<SqlParameter> paraList)
        {
            string sql = "select max(id) from fanli_gift";
            if (strWhere.Length > 0) sql += " where " + strWhere;
            return worker.ExecuteCount(sql);
        }

        #endregion

        #region Exists
        /// <summary>
        /// 是否存在该记录 (如不需要条件，可以设置strWhere为空，paraList为null )
        /// </summary>
        public bool Exists(string strWhere, List<SqlParameter> paraList)
        {
            string sql = "select count(*) from fanli_gift";
            if (strWhere.Length > 0) sql += " where " + strWhere;
            int num = worker.ExecuteCount(sql);
            return num == 0 ? false : true;
        } 
        #endregion

        #region Add
        /// <summary>
        /// 增加一条数据,返回自增的id
        /// </summary>
        public int Add(fanli.Model.fanli_gift model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into fanli_gift(");
            strSql.Append("parentCataid,cataid,giftName,picurl,about,notice,score,money,remainNum,useCondition,hasSaleNum,restriction,exchangeCondition,ishot,sortid,status,shopID,ext1,ext2)");
            strSql.Append(" values (");
            strSql.Append(" @parentCataid, @cataid, @giftName, @picurl, @about, @notice, @score, @money, @remainNum, @useCondition, @hasSaleNum, @restriction, @exchangeCondition, @ishot, @sortid, @status, @shopID, @ext1, @ext2)");
            //strSql.Append(";select @@IDENTITY");
            List<SqlParameter> list = new List<SqlParameter>();
			list.Add(new SqlParameter("@parentCataid",model.parentCataid ));
			list.Add(new SqlParameter("@cataid",model.cataid ));
			list.Add(new SqlParameter("@giftName",model.giftName ));
			list.Add(new SqlParameter("@picurl",model.picurl ));
			list.Add(new SqlParameter("@about",model.about ));
			list.Add(new SqlParameter("@notice",model.notice ));
			list.Add(new SqlParameter("@score",model.score ));
			list.Add(new SqlParameter("@money",model.money ));
			list.Add(new SqlParameter("@remainNum",model.remainNum ));
			list.Add(new SqlParameter("@useCondition",model.useCondition ));
			list.Add(new SqlParameter("@hasSaleNum",model.hasSaleNum ));
			list.Add(new SqlParameter("@restriction",model.restriction ));
			list.Add(new SqlParameter("@exchangeCondition",model.exchangeCondition ));
			list.Add(new SqlParameter("@ishot",model.ishot ));
			list.Add(new SqlParameter("@sortid",model.sortid ));
			list.Add(new SqlParameter("@status",model.status ));
			list.Add(new SqlParameter("@shopID",model.shopID ));
			list.Add(new SqlParameter("@ext1",model.ext1 ));
			list.Add(new SqlParameter("@ext2",model.ext2 ));
            return worker.ExecuteInsert(strSql.ToString(), list);
        } 
        #endregion

        #region Update
        /// <summary>
        /// 更新一条数据,返回影响的行数（建议重载后使用）
        /// </summary>
        public int Update(fanli.Model.fanli_gift model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update fanli_gift set ");
            strSql.Append("parentCataid=@parentCataid,");
            strSql.Append("cataid=@cataid,");
            strSql.Append("giftName=@giftName,");
            strSql.Append("picurl=@picurl,");
            strSql.Append("about=@about,");
            strSql.Append("notice=@notice,");
            strSql.Append("score=@score,");
            strSql.Append("money=@money,");
            strSql.Append("remainNum=@remainNum,");
            strSql.Append("useCondition=@useCondition,");
            strSql.Append("hasSaleNum=@hasSaleNum,");
            strSql.Append("restriction=@restriction,");
            strSql.Append("exchangeCondition=@exchangeCondition,");
            strSql.Append("ishot=@ishot,");
            strSql.Append("sortid=@sortid,");
            strSql.Append("status=@status,");
            strSql.Append("shopID=@shopID,");
            strSql.Append("ext1=@ext1,");
            strSql.Append("ext2=@ext2");
            strSql.Append(" where id=@id ");

            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("@id", model.id));//id
            list.Add(new SqlParameter("@parentCataid", model.parentCataid));//父分类id
            list.Add(new SqlParameter("@cataid", model.cataid));//子分类id
            list.Add(new SqlParameter("@giftName", model.giftName));//礼品名称
            list.Add(new SqlParameter("@picurl", model.picurl));//图片地址
            list.Add(new SqlParameter("@about", model.about));//介绍
            list.Add(new SqlParameter("@notice", model.notice));//注意事项
            list.Add(new SqlParameter("@score", model.score));//所需积分
            list.Add(new SqlParameter("@money", model.money));//所需返利
            list.Add(new SqlParameter("@remainNum", model.remainNum));//库存量
            list.Add(new SqlParameter("@useCondition", model.useCondition));//使用条件
            list.Add(new SqlParameter("@hasSaleNum", model.hasSaleNum));//已兑换数量
            list.Add(new SqlParameter("@restriction", model.restriction));//兑换限制
            list.Add(new SqlParameter("@exchangeCondition", model.exchangeCondition));//兑换条件
            list.Add(new SqlParameter("@ishot", model.ishot));//热门推荐
            list.Add(new SqlParameter("@sortid", model.sortid));//排序
            list.Add(new SqlParameter("@status", model.status));//状态
            list.Add(new SqlParameter("@shopID", model.shopID));//关联商城id
            list.Add(new SqlParameter("@ext1", model.ext1));//扩展1
            list.Add(new SqlParameter("@ext2", model.ext2));//扩展2

            return worker.ExecuteNonQuery(strSql.ToString(), list);
        }

        /// <summary>
        /// 执行sql语句并返回影响记录条数（请在扩展方法中写新函数，不建议直接调用）
        /// </summary>
        public int Update(string strUpdateSql, List<SqlParameter> cmdParms)
        {
            strUpdateSql = strUpdateSql.Replace("[tbl]", "fanli_gift");
            return worker.ExecuteNonQuery(strUpdateSql, cmdParms);
        }

        #endregion

        #region Delete
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from fanli_gift ");
            strSql.Append(" where id=@id ");
            List<SqlParameter> list = new List<SqlParameter>();
            list.Add(new SqlParameter("@id", id));

            return worker.ExecuteNonQuery(strSql.ToString(), list);
        }

        /// <summary>
        /// 根据where语句删除数据
        /// </summary>
        public int Delete(string strWhere, List<SqlParameter> paraList)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from fanli_gift ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where ");
                strSql.Append(strWhere);
            }
            return worker.ExecuteNonQuery(strSql.ToString(), paraList);
        } 
        #endregion

        #region  获得一个字段值
        /// <summary>
        /// 获得数据列表(参数化)
        /// </summary>
        public string GetField(string fieldName, string strWhere, List<SqlParameter> cmdParms)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 ");
            strSql.Append(fieldName);
            strSql.Append(" FROM fanli_gift ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.ExecuteScalar(strSql.ToString(), cmdParms);
        }


        /// <summary>
        /// 获得记录条数count(*)
        /// </summary>
        public int GetCount(string strWhere, List<SqlParameter> cmdParms)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) ");
            strSql.Append(" FROM fanli_gift ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.ExecuteCount(strSql.ToString(), cmdParms);
        }
        #endregion

        #region  获得一条记录

        /// <summary>
        /// 用sql语句返回一个datarow（请在扩展方法中写新函数，不建议直接调用）
        /// </summary>
        public DataRow GetRowBySql(string strSelectSql, List<SqlParameter> cmdParams)
        {
            return worker.ExecuteOneRow(strSelectSql, cmdParams);
        }

        /// <summary>
        /// 获得一条记录
        /// </summary>
        public DataRow GetRow(string strWhere, List<SqlParameter> cmdParms,string dataFields)
        {
            StringBuilder strSql = new StringBuilder();
            if (dataFields == null|| dataFields=="")  dataFields = "id,parentCataid,cataid,giftName,picurl,about,notice,score,money,remainNum,useCondition,hasSaleNum,restriction,exchangeCondition,ishot,sortid,status,shopID,ext1,ext2";
            strSql.Append("select top 1 ");
            strSql.Append(dataFields);
            strSql.Append(" FROM fanli_gift ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.ExecuteOneRow(strSql.ToString(), cmdParms);
        }

        /// <summary>
        /// 获得一条记录model
        /// </summary>
        public Model.fanli_gift GetModel(string strWhere, List<SqlParameter> cmdParms)
        {
            DataRow dr = GetRow(strWhere, cmdParms ,"");
            return DatarowToModel(dr);
        }

        /// <summary>
        /// 获得一条记录model
        /// </summary>
        public Model.fanli_gift GetModel(int id)
        {
            List<SqlParameter> paraList = new List<SqlParameter>();
            paraList.Add(new SqlParameter("@id", id));
            DataRow dr = GetRow("id=@id", paraList ,"");
            return DatarowToModel(dr);
        }
        #endregion

        #region  获得dataset列表
        /// <summary>
        /// 用sql语句返回一个dataset
        /// </summary>
        /// <param name="strSelectSql">查询sql语句</param>
        public DataSet GetDatasetBySql(string strSelectSql)
        {
            return worker.Execute(strSelectSql);
        }

        /// <summary>
        /// 用sql语句返回一个dataset
        /// </summary>
        /// <param name="strSelectSql">查询sql语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        public DataSet GetDatasetBySql(string strSelectSql, List<SqlParameter> cmdParms)
        {
            return worker.Execute(strSelectSql, cmdParms);
        }


        /// <summary>
        /// 根据页码获取当前一页的数据
        /// </summary>
        /// <param name="currentPage">页码</param>
        /// <param name="pageSize">每页的记录条数</param>
        /// <param name="strWhere">查询子句，不包括order by</param>
        /// <param name="strOrderBy">查询order by子句</param>
        /// <param name="cmdParms">参数列表</param>
        /// <param name="dataFields">要查询的字段列表，为空表示所有字段</param>
        /// <param name="tblName">表名，支持多表</param>
        public DataSet GetDatasetByPageSql(int currentPage, int pageSize, string strWhere, string strOrderBy, List<SqlParameter> cmdParms, string dataFields, string tblName)
        {
            return worker.ExecutePageSql(currentPage, pageSize, strWhere, strOrderBy, cmdParms, dataFields, tblName);
        }
        /// <summary>
        /// 根据页码获取当前一页的数据
        /// </summary>
        /// <param name="currentPage">页码</param>
        /// <param name="pageSize">每页的记录条数</param>
        /// <param name="strWhere">查询子句，不包括order by</param>
        /// <param name="strOrderBy">查询order by子句</param>
        /// <param name="cmdParms">参数列表</param>
        /// <param name="dataFields">要查询的字段列表，为空表示所有字段</param>
        public DataSet GetDatasetByPageSql(int currentPage, int pageSize, string strWhere, string strOrderBy, List<SqlParameter> cmdParms, string dataFields)
        {
            return GetDatasetByPageSql(currentPage, pageSize, strWhere, strOrderBy, cmdParms, dataFields, "fanli_gift");
        }

        /// <summary>
        /// 获得dataset列表
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        public DataSet GetDataset(string strWhere)
        {
            return GetDataset(strWhere, null, "");
        }
        
        /// <summary>
        /// 获得dataset列表
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        /// <param name="dataFields">获取的字段列表，传递空则表示所有</param>
        public DataSet GetDataset(string strWhere, List<SqlParameter> cmdParms,string dataFields)
        {
            StringBuilder strSql = new StringBuilder();
            if (dataFields == "") dataFields = "id,parentCataid,cataid,giftName,picurl,about,notice,score,money,remainNum,useCondition,hasSaleNum,restriction,exchangeCondition,ishot,sortid,status,shopID,ext1,ext2";
            strSql.Append("select ");
            strSql.Append(dataFields);
            strSql.Append(" FROM fanli_gift ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.Execute(strSql.ToString(), cmdParms);
        }
        
        /// <summary>
        /// 获得dataset列表
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        /// <param name="dataFields">获取的字段列表，传递空则表示所有</param>
        /// <param name="topN">获取的条数</param>
        public DataSet GetDataset(string strWhere, List<SqlParameter> cmdParms,string dataFields,int topN)
        {
            StringBuilder strSql = new StringBuilder();
            if (dataFields == "") dataFields = "id,parentCataid,cataid,giftName,picurl,about,notice,score,money,remainNum,useCondition,hasSaleNum,restriction,exchangeCondition,ishot,sortid,status,shopID,ext1,ext2";
            strSql.Append("select ");
            strSql.Append(dataFields);
            strSql.Append(" FROM fanli_gift ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return worker.ExecuteTop(strSql.ToString(), cmdParms, topN);
        }

        /// <summary>
        /// 获得modellist
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        public List<Model.fanli_gift> GetModelList(string strWhere)
        {
            DataSet dset = GetDataset(strWhere, null, "");
            if (dset == null) return null;
            else  return DatatableToModelList(dset.Tables[0]);
        }

        /// <summary>
        /// 获得modellist
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        /// <param name="dataFields">获取的字段列表，传递空则表示所有</param>
        public List<Model.fanli_gift> GetModelList(string strWhere, List<SqlParameter> cmdParms, string dataFields)
        {
            DataSet dset = GetDataset(strWhere, cmdParms, dataFields);
            if (dset == null) return null;
            else  return DatatableToModelList(dset.Tables[0]);
        }

        /// <summary>
        /// 获得modellist
        /// </summary>
        /// <param name="strWhere">查询条件，可带order by语句</param>
        /// <param name="cmdParms">输入参数列表</param>
        /// <param name="dataFields">获取的字段列表，传递空则表示所有</param>
        /// <param name="topN">获取的条数</param>
        public List<Model.fanli_gift> GetModelList(string strWhere, List<SqlParameter> cmdParms, string dataFields, int topN)
        {
            DataSet dset = GetDataset(strWhere, cmdParms, dataFields, topN);
            if (dset == null) return null;
            else return DatatableToModelList(dset.Tables[0]);
        }
        #endregion

        #region  change to model
        /// <summary>
        /// 把数据从datatable转存成modellist
        /// </summary>
        public List<Model.fanli_gift> DatatableToModelList(DataTable dt)
        {
            List<Model.fanli_gift> modelList = new List<Model.fanli_gift>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                for (int n = 0; n < rowsCount; n++)
                {
                    Model.fanli_gift model = new Model.fanli_gift();
                    model = DatarowToModel(dt.Rows[n]);
                    modelList.Add(model);
                }
            }
            return modelList;
        }
        /// <summary>
        /// 把数据从datarow转存成model
        /// </summary>
        public fanli.Model.fanli_gift DatarowToModel(DataRow dr)
        {
            Model.fanli_gift model = new Model.fanli_gift();
            model.id = dr["id"] == DBNull.Value ? 0 : int.Parse(dr["id"].ToString());//id
            model.parentCataid = dr["parentCataid"] == DBNull.Value ? 0 : int.Parse(dr["parentCataid"].ToString());//父分类id
            model.cataid = dr["cataid"] == DBNull.Value ? 0 : int.Parse(dr["cataid"].ToString());//子分类id
            model.giftName = dr["giftName"].ToString();//礼品名称
            model.picurl = dr["picurl"].ToString();//图片地址
            model.about = dr["about"].ToString();//介绍
            model.notice = dr["notice"].ToString();//注意事项
            model.score = dr["score"] == DBNull.Value ? 0 : int.Parse(dr["score"].ToString());//所需积分
            model.money = dr["money"] == DBNull.Value ? 0 : float.Parse(dr["money"].ToString());//所需返利
            model.remainNum = dr["remainNum"] == DBNull.Value ? 0 : int.Parse(dr["remainNum"].ToString());//库存量
            model.useCondition = dr["useCondition"].ToString();//使用条件
            model.hasSaleNum = dr["hasSaleNum"] == DBNull.Value ? 0 : int.Parse(dr["hasSaleNum"].ToString());//已兑换数量
            model.restriction = dr["restriction"].ToString();//兑换限制
            model.exchangeCondition = dr["exchangeCondition"].ToString();//兑换条件
            model.ishot = dr["ishot"] == DBNull.Value ? 0 : int.Parse(dr["ishot"].ToString());//热门推荐
            model.sortid = dr["sortid"] == DBNull.Value ? 0 : int.Parse(dr["sortid"].ToString());//排序
            model.status = dr["status"] == DBNull.Value ? 0 : int.Parse(dr["status"].ToString());//状态
            model.shopID = dr["shopID"] == DBNull.Value ? 0 : int.Parse(dr["shopID"].ToString());//关联商城id
            model.ext1 = dr["ext1"].ToString();//扩展1
            model.ext2 = dr["ext2"].ToString();//扩展2
            return model;
        }


        #endregion

        #endregion

        #region 自定义方法（非系统生成的方法，请在此区域单独写函数，以便维护）

        /// <summary>
        /// 函数功能：示例函数
        /// 作者：buzhizhe 创建时间：2011.6.22
        /// 修改者：buzhizhe 最后修改时间：2011.6.22
        /// </summary>
        public void YourFunction()
        {
            ///
        } 


        #endregion
    }
}



